USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdCity')
	BEGIN
		DROP  Procedure  dbo.InsUpdCity
	END
GO

CREATE Procedure dbo.InsUpdCity (
	@CITYNO					VARCHAR(10)
,	@CITYNAME				VARCHAR(100)
,	@USERGUID				VARCHAR(100)
,	@ISACTIVE				VARCHAR(10) = 'X'
-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
)
AS
BEGIN

	SET @ISACTIVE		= CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '' END
	
	IF( CAST(@CITYNO AS INT) <= 0) AND EXISTS(SELECT 1 FROM TR_CITY WHERE CITYNAME=@CITYNAME AND USERGUID=@USERGUID)
	BEGIN
		SELECT 
			'<DATA>
				<RESPONSE>
					<RESULT>E</RESULT>
					<MESSAGE><![CDATA[City already exists with same name]]></MESSAGE>
				</RESPONSE>
			</DATA>' AS Result
		RETURN
	END


	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_CITY WHERE CityNo = CAST(@CityNo AS INT))
			BEGIN		-- UPDATE
				UPDATE TR_CITY
				SET CityName	= @CITYNAME
				,	IsActive	= @ISACTIVE
				WHERE
					CITYNO		= @CITYNO
				AND	CAST(USERGUID	AS VARCHAR(100)) = @USERGUID
			END
			ELSE
			BEGIN		-- INSERT 
				SELECT @CITYNO	= ISNULL(MAX(CITYNO), 0) +1 FROM TR_CITY

				INSERT INTO TR_CITY (CITYNO,CITYNAME,ISACTIVE,USERGUID)
				VALUES(
					@CITYNO			
				,	@CITYNAME		
				,	@ISACTIVE		
				,	@USERGUID		
				)
			END
			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>City details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
			END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save City Details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
